package form;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import chucnang.MuaHang;
import chucnang.TonKho;
import chucnang.TraTien;
import chucnang.ChungTu;
import trangchu.TrangChu;
import trangchu.UtilDelegate;
import trogiup.DSJGetLienHe;

public class DSJ_FormMuaHang extends UtilDelegate {

	private TrangChu trangchu;
	private TraTien tratien;
	private ChungTu chungtu;
	private TonKho tonkho;
	public String NhapHang_ID;	
	public String MaSP;
	public String NhomSP;
	public String TenSP;
	public String DonViSP;
	public String SoLuong;
	public String Kho;
	public String GiaVon;
	public String GiaBan;
	public String NgayNhapHang;
	public String HanThanhToan;
	public String ThanhTien;
	public String LoaiChungTu;
	public String ChungTu;
	public String GhiChu;
	public String ID_GDMuaHang;
	public String TongThanhToan;
	public String DaTra;
	public String ConLai;
	
	public String getID_GDMuaHang() {
		return ID_GDMuaHang;
	}
	public void setID_GDMuaHang(String iD_GDMuaHang) {
		ID_GDMuaHang = iD_GDMuaHang;
	}
	public String getTongThanhToan() {
		return TongThanhToan;
	}
	public void setTongThanhToan(String tongThanhToan) {
		TongThanhToan = tongThanhToan;
	}
	public String getDaTra() {
		return DaTra;
	}
	public void setDaTra(String daTra) {
		DaTra = daTra;
	}
	public String getConLai() {
		return ConLai;
	}
	public void setConLai(String conLai) {
		ConLai = conLai;
	}
	public String getGhiChu() {
		return GhiChu;
	}
	public void setGhiChu(String ghiChu) {
		GhiChu = ghiChu;
	}
	public String getChungTu() {
		return ChungTu;
	}
	public void setChungTu(String chungTu) {
		ChungTu = chungTu;
	}
	public String getLoaiChungTu() {
		return LoaiChungTu;
	}
	public void setLoaiChungTu(String loaiChungTu) {
		LoaiChungTu = loaiChungTu;
	}
	public String getNhapHang_ID() {
		return NhapHang_ID;
	}
	public void setNhapHang_ID(String nhapHang_ID) {
		NhapHang_ID = nhapHang_ID;
	}
	public String getThanhTien() {
		return ThanhTien;
	}
	public void setThanhTien(String thanhTien) {
		ThanhTien = thanhTien;
	}
	public String getHanThanhToan() {
		return HanThanhToan;
	}
	public void setHanThanhToan(String hanThanhToan) {
		HanThanhToan = hanThanhToan;
	}
	public String getNgayNhapHang() {
		return NgayNhapHang;
	}
	public void setNgayNhapHang(String ngayNhapHang) {
		NgayNhapHang = ngayNhapHang;
	}
	public String getMaSP() {
		return MaSP;
	}
	public void setMaSP(String maSP) {
		MaSP = maSP;
	}
	public String getNhomSP() {
		return NhomSP;
	}
	public void setNhomSP(String nhomSP) {
		NhomSP = nhomSP;
	}
	public String getTenSP() {
		return TenSP;
	}
	public void setTenSP(String tenSP) {
		TenSP = tenSP;
	}
	public String getDonViSP() {
		return DonViSP;
	}
	public void setDonViSP(String donViSP) {
		DonViSP = donViSP;
	}
	public String getSoLuong() {
		return SoLuong;
	}
	public void setSoLuong(String soLuong) {
		SoLuong = soLuong;
	}
	public String getKho() {
		return Kho;
	}
	public void setKho(String kho) {
		Kho = kho;
	}
	public String getGiaVon() {
		return GiaVon;
	}
	public void setGiaVon(String giaVon) {
		GiaVon = giaVon;
	}
	public String getGiaBan() {
		return GiaBan;
	}
	public void setGiaBan(String giaBan) {
		GiaBan = giaBan;
	}
	//select hanghoa
	public Vector<DSJ_FormMuaHang> getUsers(){
		Vector<DSJ_FormMuaHang> getMuaHang = new Vector<DSJ_FormMuaHang>();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("select * from HangHoa " +
					"join DonVi on HangHoa.DonViSP = DonVi.MaDV " +
					"join DiaChiKho on HangHoa.Kho = DiaChiKho.MaK " +
					"join NhomHang on HangHoa.NhomSP = NhomHang.MaNH ");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				
				String MaSP= rs.getString("MaSP");
				String TenSP = rs.getString("TenSP");
				String NhomSP = rs.getString("TenNH");				
				String DonViSP = rs.getString("TenDV");
				String SoLuong = rs.getString("SoLuong");
				String Kho = rs.getString("TenKho");
				String GiaVon = rs.getString("GiaVon");
				String GiaBan = rs.getString("GiaBan");
				
				DSJ_FormMuaHang b = new DSJ_FormMuaHang();
				b.setMaSP(MaSP);
				b.setTenSP(TenSP);
				b.setNhomSP(NhomSP);				
				b.setDonViSP(DonViSP);
				b.setSoLuong(SoLuong);
				b.setKho(Kho);
				b.setGiaVon(GiaVon);
				b.setGiaBan(GiaBan);
				
				getMuaHang.add(b);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return getMuaHang;
	}
	public Vector getLoad(){
		Vector<String> b = new Vector<String>();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("select top 1 * from (select top (select count(*) from NhapHang) * from NhapHang  order by NhapHang_ID DESC)a");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				
				b.add(rs.getString("HangHoa_ID"));
				b.add(rs.getString("TenHangHoa"));
				b.add(rs.getString("KhoHang"));
				b.add(rs.getString("TenDonVi"));
				b.add(rs.getString("DonGia"));
				b.add(rs.getString("SoLuong"));
				b.add(rs.getString("ThanhTien"));
				b.add(rs.getString("NhapHang_ID"));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return b;
	}
	public Vector getEdit(String ID){
		Integer id = Integer.parseInt(ID);
		Vector<String> b = new Vector<String>();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("select * from NhapHang where NhapHang_ID="+id);
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				b.add(rs.getString("HangHoa_ID"));
				b.add(rs.getString("TenHangHoa"));
				b.add(rs.getString("KhoHang"));
				b.add(rs.getString("TenDonVi"));
				b.add(rs.getString("DonGia"));
				b.add(rs.getString("SoLuong"));
				b.add(rs.getString("ThanhTien"));
				b.add(rs.getString("NhapHang_ID"));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return b;
	}
	public void insertInto(DSJ_FormMuaHang person,DSJ_NhanVien nhanvien,DSJ_DieuKhoanThanhToan dieukhoan,DSJ_NPPMuaHang nhaphanphoi,DSJ_KhoHang kho,DSJ_DonVi donvi){
		Connection con =UtilDelegate.getConnection();
		try {
			Statement st = con.createStatement();
			String str ="INSERT INTO NhapHang(HangHoa_ID,TenHangHoa,KhoHang,TenDonVi,DonGia,SoLuong,ThanhTien,NhanVienNH,NgayNhapHang,NhaPhanPhoi_ID,TenNPP,CachThanhToan,HanThanToan,LoaiChungTu,ChungTu,GhiChu) " + "VALUES ('"+ person.getMaSP()+ "',N'"+ person.getTenSP()+ "',N'"+ kho.getTenKho()+ "'," +
			"N'"+ donvi.getTenDV()+ "', '"+ person.getGiaVon()+ "', '"+ person.getSoLuong()+ "'," +
			" '"+ person.getThanhTien()+ "', N'"+nhanvien.getHoTenNV()+ "' , '"+ person.getNgayNhapHang()+ "'," +
			"'"+ nhaphanphoi.getMaCC()+ "',N'"+ nhaphanphoi.getNhaCungCap()+ "', N'"+ dieukhoan.getTenDK()+ "'," +
			" '"+ person.getHanThanhToan()+ "', N'"+ person.getLoaiChungTu()+ "', '"+ person.getChungTu()+ "', N'"+ person.getGhiChu()+ "')";	
			st.execute(str);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			UtilDelegate.closeConnection(con);
		}
	}
	public void edit(DSJ_DonVi donvi,DSJ_KhoHang kho,String ID){
		Integer id = Integer.parseInt(ID);
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("UPDATE NhapHang SET HangHoa_ID=?, TenHangHoa=?,KhoHang=?,TenDonVi=?,DonGia=?,SoLuong=?,ThanhTien=? WHERE NhapHang_ID=?");
			st.setInt(8, id);
			st.setString(1, this.MaSP);
			st.setString(2, this.TenSP);
			st.setString(3, kho.TenKho);
			st.setString(4, donvi.TenDV);	
			st.setString(5, this.GiaVon);
			st.setString(6, this.SoLuong);
			st.setString(7, this.ThanhTien);
			st.executeUpdate();			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			UtilDelegate.closeConnection(con);
		}
		
	}
	public void del(String ID){
		Integer id = Integer.parseInt(ID);
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("Delete FROM NhapHang WHERE NhapHang_ID ="+id);
			//st.setString(1, ID);			
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}
	public void updatehanghoa(DSJ_FormMuaHang data){
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("UPDATE HangHoa SET SoLuong=? WHERE MaSP=?");
			st.setString(2, this.MaSP);
			st.setString(1, this.SoLuong);
			st.executeUpdate();			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			UtilDelegate.closeConnection(con);
		}
	}
	public String selecthanghoa(DSJ_FormMuaHang data){
		String SoLuong1 = null;
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("Select SoLuong from HangHoa WHERE MaSP="+data.MaSP);
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				 SoLuong1 = rs.getString("SoLuong");
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			UtilDelegate.closeConnection(con);
		}
		return SoLuong1;
	}
	
	public String selectmaNH(DSJ_FormMuaHang data){
		String iD_GDMuaHang = null;
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("select top 1 ID_GDMuaHang from (select top (select count(*) from KetThucGDMuaHang) * from KetThucGDMuaHang order by ID_GDMuaHang DESC)a");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				iD_GDMuaHang = rs.getString("ID_GDMuaHang");
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			UtilDelegate.closeConnection(con);
		}
		return iD_GDMuaHang;
	}
	
	//tra tien
		public void insertGiaoDichMuaHang(DSJ_FormMuaHang muahang,DSJ_NPPMuaHang nhapp){
			Connection con =UtilDelegate.getConnection();
			try {
				Statement st = con.createStatement();
				String str ="INSERT INTO KetThucGDMuaHang(ID_NhaPP,TenNhaPP,ChungTu,Ngay,TongThanhToan,DaTra,ConLai,GhiChu,LoaiChungTu) " + "VALUES (" +
				"'"+ nhapp.getMaCC()+ "',N'"+ nhapp.getNhaCungCap()+ "','"+ muahang.getChungTu()+ "'," +
				"'"+ muahang.getNgayNhapHang()+ "', '"+ muahang.getTongThanhToan()+ "', '"+ muahang.getDaTra()+ "'," +
				" '"+ muahang.getConLai()+ "', N'"+muahang.getGhiChu()+ "', N'"+muahang.getLoaiChungTu()+ "')";	
				st.execute(str);
			} catch (Exception e) {
				e.printStackTrace();
			}finally{
				UtilDelegate.closeConnection(con);
			}
		}
	
	public Vector getTraTien(){
		Vector list = new Vector();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("SELECT * FROM KetThucGDMuaHang");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Vector<String> b = new Vector<String>();
				b.add(rs.getString("ChungTu"));
				b.add(rs.getString("ID_NhaPP"));
				b.add(rs.getString("TenNhaPP"));
				b.add(rs.getString("Ngay"));
				b.add(rs.getString("TongThanhToan"));
				b.add(rs.getString("DaTra"));
				b.add(rs.getString("ConLai"));
				b.add(rs.getString("GhiChu"));
				b.add(rs.getString("LoaiChungTu"));
				list.add(b);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return list;
	}
	
	
public Vector getTraTienxem(TraTien tratien){
		this.tratien =tratien;
		java.util.Date datestart =  tratien.dateStart.getDate();
		String dates = String.format("%1$td-%1$tm-%1$tY", datestart);
		java.util.Date dateend =  tratien.dateEnd.getDate();
		String dated = String.format("%1$td-%1$tm-%1$tY", dateend);
		Vector list = new Vector();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("SELECT * FROM KetThucGDMuaHang WHERE Ngay BETWEEN '" +dates+ "' AND '" +dated+"'");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Vector<String> b = new Vector<String>();
				b.add(rs.getString("ChungTu"));
				b.add(rs.getString("ID_NhaPP"));
				b.add(rs.getString("TenNhaPP"));
				b.add(rs.getString("Ngay"));
				b.add(rs.getString("TongThanhToan"));
				b.add(rs.getString("DaTra"));
				b.add(rs.getString("ConLai"));
				b.add(rs.getString("GhiChu"));
				b.add(rs.getString("LoaiChungTu"));
				list.add(b);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return list;
	}
public Vector getChungTuxem(ChungTu chungtu){
	this.chungtu =chungtu;
	java.util.Date datestart =  chungtu.dateStart.getDate();
	String dates = String.format("%1$td-%1$tm-%1$tY", datestart);
	java.util.Date dateend =  chungtu.dateEnd.getDate();
	String dated = String.format("%1$td-%1$tm-%1$tY", dateend);
	Vector list = new Vector();
	Connection con =UtilDelegate.getConnection();		
	PreparedStatement st;
	try {
		st = con.prepareStatement("SELECT * FROM KetThucGDMuaHang WHERE Ngay BETWEEN '" +dates+ "' AND '" +dated+"'");
		ResultSet rs = st.executeQuery();
		while(rs.next()){
			Vector<String> b = new Vector<String>();
			b.add(rs.getString("ChungTu"));
			b.add(rs.getString("ID_NhaPP"));
			b.add(rs.getString("TenNhaPP"));
			b.add(rs.getString("Ngay"));
			b.add(rs.getString("TongThanhToan"));
			b.add(rs.getString("DaTra"));
			b.add(rs.getString("ConLai"));
			b.add(rs.getString("GhiChu"));
			b.add(rs.getString("LoaiChungTu"));
			list.add(b);
		}
	} catch (SQLException e) {
		
		e.printStackTrace();
	}
	
	return list;
}
	//tonkho
	public Vector getKhoHangHoa(TonKho tonkho){
	this.tonkho = tonkho;	
	Vector list = new Vector();
	Connection con =UtilDelegate.getConnection();		
	PreparedStatement st;
	try {
		st = con.prepareStatement("select * from HangHoa " +
				"join DonVi on HangHoa.DonViSP = DonVi.MaDV " +
				"join DiaChiKho on HangHoa.Kho = DiaChiKho.MaK " +
				"join NhomHang on HangHoa.NhomSP = NhomHang.MaNH " +
				"Where TenKho=N'"+tonkho.cmbKhoHang.getSelectedItem().toString()+"'");
		
		ResultSet rs = st.executeQuery();
		while(rs.next()){
			Vector<String> b = new Vector<String>();
			b.add(rs.getString("MaSP"));
			b.add(rs.getString("TenSP"));
			b.add(rs.getString("TenKho"));
			b.add(rs.getString("SoLuong"));
			b.add(rs.getString("TenDV"));
			b.add(rs.getString("TenNH"));
			list.add(b);
		}
	} catch (SQLException e) {
		
		e.printStackTrace();
	}
	
	return list;
}
	//tonkho
	public Vector getHangHoa(){
	this.tonkho = tonkho;	
	Vector list = new Vector();
	Connection con =UtilDelegate.getConnection();		
	PreparedStatement st;
	try {
		st = con.prepareStatement("select * from HangHoa " +
				"join DonVi on HangHoa.DonViSP = DonVi.MaDV " +
				"join DiaChiKho on HangHoa.Kho = DiaChiKho.MaK " +
				"join NhomHang on HangHoa.NhomSP = NhomHang.MaNH");
		
		ResultSet rs = st.executeQuery();
		while(rs.next()){
			Vector<String> b = new Vector<String>();
			b.add(rs.getString("MaSP"));
			b.add(rs.getString("TenSP"));
			b.add(rs.getString("TenKho"));
			b.add(rs.getString("SoLuong"));
			b.add(rs.getString("TenDV"));
			b.add(rs.getString("TenNH"));
			list.add(b);
		}
	} catch (SQLException e) {
		
		e.printStackTrace();
	}
	
	return list;
}
}
